from flask import Blueprint, request, jsonify
from flask_jwt_extended import jwt_required, get_jwt_identity
from app.models import db
from app.models.user import User
from sqlalchemy import text
from datetime import datetime
import json

system_configs_bp = Blueprint('system_configs', __name__)

@system_configs_bp.route('', methods=['GET'])
@jwt_required()
def get_system_configs():
    """获取系统配置列表"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user:
            return jsonify({'error': '用户不存在'}), 404
        
        category = request.args.get('category', '')
        search = request.args.get('search', '')
        is_public = request.args.get('is_public')
        
        # 非管理员只能查看公开配置
        if user.role not in ['admin', 'manager']:
            is_public = '1'
        
        # 构建查询条件
        conditions = []
        params = {}
        
        if category:
            conditions.append('category = :category')
            params['category'] = category
        
        if search:
            conditions.append('(config_key LIKE :search OR description LIKE :search)')
            params['search'] = f'%{search}%'
        
        if is_public is not None:
            conditions.append('is_public = :is_public')
            params['is_public'] = int(is_public)
        
        where_clause = 'WHERE ' + ' AND '.join(conditions) if conditions else ''
        
        query = text(f"""
            SELECT id, config_key, config_value, category, description, 
                   config_type, is_public, is_editable, created_at, updated_at
            FROM system_configs 
            {where_clause}
            ORDER BY category, config_key
        """)
        
        result = db.session.execute(query, params)
        configs = []
        
        for row in result:
            # 根据数据类型解析配置值
            config_value = row.config_value
            if row.config_type == 'json' and config_value:
                try:
                    config_value = json.loads(config_value)
                except json.JSONDecodeError:
                    pass
            elif row.config_type == 'boolean':
                config_value = config_value.lower() in ['true', '1', 'yes'] if config_value else False
            elif row.config_type == 'integer':
                try:
                    config_value = int(config_value) if config_value else 0
                except ValueError:
                    config_value = 0
            elif row.config_type == 'float':
                try:
                    config_value = float(config_value) if config_value else 0.0
                except ValueError:
                    config_value = 0.0
            
            configs.append({
                'id': row.id,
                'config_key': row.config_key,
                'config_value': row.config_value,
                'category': row.category,
                'description': row.description,
                'data_type': row.config_type,
                'is_public': bool(row.is_public),
                'is_editable': bool(row.is_editable),
                'created_at': row.created_at.isoformat() if hasattr(row.created_at, 'isoformat') and row.created_at else str(row.created_at) if row.created_at else None,
                'updated_at': row.updated_at.isoformat() if hasattr(row.updated_at, 'isoformat') and row.updated_at else str(row.updated_at) if row.updated_at else None
            })
        
        return jsonify({
            'configs': configs,
            'total': len(configs)
        }), 200
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@system_configs_bp.route('/categories', methods=['GET'])
@jwt_required()
def get_config_categories():
    """获取配置分类列表"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user:
            return jsonify({'error': '用户不存在'}), 404
        
        # 构建查询条件
        where_clause = ''
        if user.role not in ['admin', 'manager']:
            where_clause = 'WHERE is_public = 1'
        
        query = text(f"""
            SELECT category, COUNT(*) as config_count,
                   COUNT(*) as active_count
            FROM system_configs 
            {where_clause}
            GROUP BY category
            ORDER BY category
        """)
        
        result = db.session.execute(query)
        categories = []
        
        for row in result:
            categories.append({
                'category': row.category,
                'config_count': row.config_count,
                'active_count': row.active_count
            })
        
        return jsonify({'categories': categories}), 200
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@system_configs_bp.route('/key/<config_key>', methods=['GET'])
@jwt_required()
def get_config_by_key(config_key):
    """根据配置键获取配置值"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user:
            return jsonify({'error': '用户不存在'}), 404
        
        # 构建查询条件
        conditions = ['config_key = :config_key']
        params = {'config_key': config_key}
        
        # 非管理员只能查看公开配置
        if user.role not in ['admin', 'manager']:
            conditions.append('is_public = 1')
        
        where_clause = 'WHERE ' + ' AND '.join(conditions)
        
        query = text(f"""
            SELECT config_value, config_type
            FROM system_configs 
            {where_clause}
        """)
        
        result = db.session.execute(query, params)
        row = result.fetchone()
        
        if not row:
            return jsonify({'error': '配置不存在或无权限访问'}), 404
        
        # 根据数据类型解析配置值
        config_value = row.config_value
        if row.config_type == 'json' and config_value:
            try:
                config_value = json.loads(config_value)
            except json.JSONDecodeError:
                pass
        elif row.config_type == 'boolean':
            config_value = config_value.lower() in ['true', '1', 'yes'] if config_value else False
        elif row.config_type == 'integer':
            try:
                config_value = int(config_value) if config_value else 0
            except ValueError:
                config_value = 0
        elif row.config_type == 'float':
            try:
                config_value = float(config_value) if config_value else 0.0
            except ValueError:
                config_value = 0.0
        
        return jsonify({
            'config_key': config_key,
            'config_value': config_value,
            'data_type': row.config_type
        }), 200
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@system_configs_bp.route('/batch', methods=['POST'])
@jwt_required()
def get_configs_batch():
    """批量获取配置"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user:
            return jsonify({'error': '用户不存在'}), 404
        
        data = request.get_json()
        config_keys = data.get('config_keys', [])
        
        if not config_keys:
            return jsonify({'error': '配置键列表不能为空'}), 400
        
        # 构建查询条件
        placeholders = ','.join([f':key_{i}' for i in range(len(config_keys))])
        conditions = [f'config_key IN ({placeholders})']
        params = {f'key_{i}': key for i, key in enumerate(config_keys)}
        
        # 非管理员只能查看公开配置
        if user.role not in ['admin', 'manager']:
            conditions.append('is_public = 1')
        
        where_clause = 'WHERE ' + ' AND '.join(conditions)
        
        query = text(f"""
            SELECT config_key, config_value, config_type
            FROM system_configs 
            {where_clause}
        """)
        
        result = db.session.execute(query, params)
        configs = {}
        
        for row in result:
            # 根据数据类型解析配置值
            config_value = row.config_value
            if row.config_type == 'json' and config_value:
                try:
                    config_value = json.loads(config_value)
                except json.JSONDecodeError:
                    pass
            elif row.config_type == 'boolean':
                config_value = config_value.lower() in ['true', '1', 'yes'] if config_value else False
            elif row.config_type == 'integer':
                try:
                    config_value = int(config_value) if config_value else 0
                except ValueError:
                    config_value = 0
            elif row.config_type == 'float':
                try:
                    config_value = float(config_value) if config_value else 0.0
                except ValueError:
                    config_value = 0.0
            
            configs[row.config_key] = config_value
        
        return jsonify({'configs': configs}), 200
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@system_configs_bp.route('', methods=['POST'])
@jwt_required()
def create_config():
    """创建系统配置"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user or user.role != 'admin':
            return jsonify({'error': '权限不足'}), 403
        
        data = request.get_json()
        
        # 验证必填字段
        required_fields = ['config_key', 'config_value', 'category', 'data_type']
        for field in required_fields:
            if field not in data or data[field] is None:
                return jsonify({'error': f'{field} 不能为空'}), 400
        
        # 验证数据类型
        valid_data_types = ['string', 'integer', 'float', 'boolean', 'json']
        if data['data_type'] not in valid_data_types:
            return jsonify({'error': f'数据类型必须是: {", ".join(valid_data_types)}'}), 400
        
        # 检查配置键是否已存在
        check_query = text("""
            SELECT COUNT(*) as count
            FROM system_configs 
            WHERE config_key = :config_key
        """)
        
        result = db.session.execute(check_query, {'config_key': data['config_key']})
        if result.scalar() > 0:
            return jsonify({'error': '配置键已存在'}), 400
        
        # 处理配置值
        config_value = data['config_value']
        if data['data_type'] == 'json':
            if isinstance(config_value, (dict, list)):
                config_value = json.dumps(config_value, ensure_ascii=False)
            elif not isinstance(config_value, str):
                config_value = str(config_value)
        else:
            config_value = str(config_value)
        
        # 插入新配置
        insert_query = text("""
            INSERT INTO system_configs (config_key, config_value, category, name, description, 
                                       config_type, is_public, is_editable, created_at)
            VALUES (:config_key, :config_value, :category, :name, :description, 
                    :config_type, :is_public, :is_editable, :created_at)
        """)
        
        db.session.execute(insert_query, {
            'config_key': data['config_key'],
            'config_value': config_value,
            'category': data['category'],
            'name': data.get('name', data['config_key']),
            'description': data.get('description'),
            'config_type': data['data_type'],
            'is_public': data.get('is_public', True),
            'is_editable': data.get('is_editable', True),
            'created_at': datetime.now()
        })
        
        db.session.commit()
        
        return jsonify({'message': '系统配置创建成功'}), 201
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': str(e)}), 500

@system_configs_bp.route('/<int:config_id>', methods=['PUT'])
@jwt_required()
def update_config(config_id):
    """更新系统配置"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user or user.role != 'admin':
            return jsonify({'error': '权限不足'}), 403
        
        data = request.get_json()
        
        # 检查配置是否存在
        check_query = text("""
            SELECT config_key, config_type FROM system_configs WHERE id = :id
        """)
        
        result = db.session.execute(check_query, {'id': config_id})
        existing_config = result.fetchone()
        
        if not existing_config:
            return jsonify({'error': '配置不存在'}), 404
        
        # 如果更新配置键，检查是否重复
        if 'config_key' in data and data['config_key'] != existing_config.config_key:
            key_check_query = text("""
                SELECT COUNT(*) as count
                FROM system_configs 
                WHERE config_key = :config_key AND id != :id
            """)
            
            result = db.session.execute(key_check_query, {
                'config_key': data['config_key'],
                'id': config_id
            })
            
            if result.scalar() > 0:
                return jsonify({'error': '配置键已存在'}), 400
        
        # 验证数据类型
        if 'data_type' in data:
            valid_data_types = ['string', 'integer', 'float', 'boolean', 'json']
            if data['data_type'] not in valid_data_types:
                return jsonify({'error': f'数据类型必须是: {", ".join(valid_data_types)}'}), 400
        
        # 构建更新字段
        update_fields = []
        params = {'id': config_id, 'updated_at': datetime.now()}
        
        allowed_fields = ['config_key', 'category', 'description', 'is_public', 'is_editable']
        for field in allowed_fields:
            if field in data:
                update_fields.append(f'{field} = :{field}')
                params[field] = data[field]
        
        # 处理data_type字段映射
        if 'data_type' in data:
            update_fields.append('config_type = :config_type')
            params['config_type'] = data['data_type']
        
        # 处理配置值
        if 'config_value' in data:
            config_value = data['config_value']
            data_type = data.get('data_type', existing_config.config_type)
            
            if data_type == 'json':
                if isinstance(config_value, (dict, list)):
                    config_value = json.dumps(config_value, ensure_ascii=False)
                elif not isinstance(config_value, str):
                    config_value = str(config_value)
            else:
                config_value = str(config_value)
            
            update_fields.append('config_value = :config_value')
            params['config_value'] = config_value
        
        if not update_fields:
            return jsonify({'error': '没有可更新的字段'}), 400
        
        # 添加更新时间
        update_fields.append('updated_at = :updated_at')
        
        # 执行更新
        update_query = text(f"""
            UPDATE system_configs 
            SET {', '.join(update_fields)}
            WHERE id = :id
        """)
        
        db.session.execute(update_query, params)
        db.session.commit()
        
        return jsonify({'message': '系统配置更新成功'}), 200
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': str(e)}), 500

@system_configs_bp.route('/<int:config_id>', methods=['DELETE'])
@jwt_required()
def delete_config(config_id):
    """删除系统配置"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user or user.role != 'admin':
            return jsonify({'error': '权限不足'}), 403
        
        # 检查配置是否存在
        check_query = text("""
            SELECT id FROM system_configs WHERE id = :id
        """)
        
        result = db.session.execute(check_query, {'id': config_id})
        if not result.fetchone():
            return jsonify({'error': '配置不存在'}), 404
        
        # 删除配置
        delete_query = text("""
            DELETE FROM system_configs WHERE id = :id
        """)
        db.session.execute(delete_query, {'id': config_id})
        
        db.session.commit()
        
        return jsonify({'message': '系统配置删除成功'}), 200
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': str(e)}), 500

@system_configs_bp.route('/batch/update', methods=['POST'])
@jwt_required()
def batch_update_configs():
    """批量更新配置"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user or user.role != 'admin':
            return jsonify({'error': '权限不足'}), 403
        
        data = request.get_json()
        configs = data.get('configs', [])
        
        if not configs:
            return jsonify({'error': '配置列表不能为空'}), 400
        
        updated_count = 0
        
        for config in configs:
            config_key = config.get('config_key')
            config_value = config.get('config_value')
            
            if not config_key:
                continue
            
            # 获取现有配置的数据类型
            type_query = text("""
                SELECT config_type FROM system_configs 
                WHERE config_key = :config_key
            """)
            
            result = db.session.execute(type_query, {'config_key': config_key})
            row = result.fetchone()
            
            if not row:
                continue
            
            # 处理配置值
            if row.config_type == 'json':
                if isinstance(config_value, (dict, list)):
                    config_value = json.dumps(config_value, ensure_ascii=False)
                elif not isinstance(config_value, str):
                    config_value = str(config_value)
            else:
                config_value = str(config_value)
            
            # 更新配置
            update_query = text("""
                UPDATE system_configs 
                SET config_value = :config_value, updated_at = :updated_at
                WHERE config_key = :config_key
            """)
            
            db.session.execute(update_query, {
                'config_key': config_key,
                'config_value': config_value,
                'updated_at': datetime.now()
            })
            
            updated_count += 1
        
        db.session.commit()
        
        return jsonify({
            'message': f'批量更新成功，共更新 {updated_count} 个配置',
            'updated_count': updated_count
        }), 200
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': str(e)}), 500

@system_configs_bp.route('/export', methods=['GET'])
@jwt_required()
def export_configs():
    """导出系统配置"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user or user.role != 'admin':
            return jsonify({'error': '权限不足'}), 403
        
        category = request.args.get('category')
        
        # 构建查询条件
        conditions = []
        params = {}
        
        if category:
            conditions.append('category = :category')
            params['category'] = category
        
        where_clause = 'WHERE ' + ' AND '.join(conditions) if conditions else ''
        
        query = text(f"""
            SELECT config_key, config_value, category, description, 
                   config_type, is_public, is_editable
            FROM system_configs 
            {where_clause}
            ORDER BY category, config_key
        """)
        
        result = db.session.execute(query, params)
        configs = []
        
        for row in result:
            configs.append({
                'config_key': row.config_key,
                'config_value': row.config_value,
                'category': row.category,
                'description': row.description,
                'data_type': row.config_type,
                'is_public': bool(row.is_public),
                'is_editable': bool(row.is_editable)
            })
        
        return jsonify({
            'configs': configs,
            'export_time': datetime.now().isoformat(),
            'total': len(configs)
        }), 200
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@system_configs_bp.route('/user-preferences', methods=['GET'])
@jwt_required()
def get_user_preferences():
    """获取当前用户的偏好设置"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user:
            return jsonify({'error': '用户不存在'}), 404
        
        # 查询用户偏好设置
        query = text("""
            SELECT config_key, config_value, config_type
            FROM system_configs 
            WHERE category = 'user_preferences' 
            AND config_key LIKE :user_prefix
            ORDER BY config_key
        """)
        
        result = db.session.execute(query, {'user_prefix': f'user_{current_user_id}_%'})
        preferences = {}
        
        for row in result:
            # 移除用户前缀，获取实际的偏好设置键
            pref_key = row.config_key.replace(f'user_{current_user_id}_', '')
            
            # 根据数据类型解析配置值
            config_value = row.config_value
            if row.config_type == 'json' and config_value:
                try:
                    config_value = json.loads(config_value)
                except json.JSONDecodeError:
                    pass
            elif row.config_type == 'boolean':
                config_value = config_value.lower() in ['true', '1', 'yes'] if config_value else False
            
            preferences[pref_key] = config_value
        
        return jsonify({'preferences': preferences}), 200
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@system_configs_bp.route('/user-preferences', methods=['POST'])
@jwt_required()
def save_user_preferences():
    """保存当前用户的偏好设置"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user:
            return jsonify({'error': '用户不存在'}), 404
        
        data = request.get_json()
        preferences = data.get('preferences', {})
        
        if not preferences:
            return jsonify({'error': '偏好设置不能为空'}), 400
        
        saved_count = 0
        
        for pref_key, pref_value in preferences.items():
            config_key = f'user_{current_user_id}_{pref_key}'
            
            # 确定数据类型
            if isinstance(pref_value, bool):
                data_type = 'boolean'
                config_value = str(pref_value).lower()
            elif isinstance(pref_value, (dict, list)):
                data_type = 'json'
                config_value = json.dumps(pref_value, ensure_ascii=False)
            else:
                data_type = 'string'
                config_value = str(pref_value)
            
            # 检查配置是否已存在
            check_query = text("""
                SELECT id FROM system_configs 
                WHERE config_key = :config_key
            """)
            
            result = db.session.execute(check_query, {'config_key': config_key})
            existing = result.fetchone()
            
            if existing:
                # 更新现有配置
                update_query = text("""
                    UPDATE system_configs 
                    SET config_value = :config_value, 
                        config_type = :config_type,
                        updated_at = :updated_at
                    WHERE config_key = :config_key
                """)
                
                db.session.execute(update_query, {
                    'config_key': config_key,
                    'config_value': config_value,
                    'config_type': data_type,
                    'updated_at': datetime.now()
                })
            else:
                # 创建新配置
                insert_query = text("""
                    INSERT INTO system_configs (config_key, config_value, category, name, 
                                               description, config_type, is_public, is_editable, created_at)
                    VALUES (:config_key, :config_value, :category, :name, 
                            :description, :config_type, :is_public, :is_editable, :created_at)
                """)
                
                db.session.execute(insert_query, {
                    'config_key': config_key,
                    'config_value': config_value,
                    'category': 'user_preferences',
                    'name': f'用户{current_user_id}的{pref_key}设置',
                    'description': f'用户{user.username}的{pref_key}偏好设置',
                    'config_type': data_type,
                    'is_public': False,
                    'is_editable': True,
                    'created_at': datetime.now()
                })
            
            saved_count += 1
        
        db.session.commit()
        
        return jsonify({
            'message': f'用户偏好设置保存成功，共保存 {saved_count} 项设置',
            'saved_count': saved_count
        }), 200
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': str(e)}), 500